﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;

namespace Workflows.Components.Reports.OpenXmlUtil
{
	/// <summary>
	/// 
	/// </summary>
	[Serializable]
	public class Excel2007ReportInstruction:InstructionBase
	{
		#region props
		private string _ExcelReportFilePath;
		/// <summary></summary>
		public string ExcelReportFilePath
		{
			get
			{
				return this._ExcelReportFilePath;
			}
			set
			{
				this._ExcelReportFilePath = value;
			}
		}

		private List<TableDataFromSql> _TableDataProviders;
		/// <summary></summary>
		public List<TableDataFromSql> TableDataProviders
		{
			get
			{
				return this._TableDataProviders;
			}
			set
			{
				this._TableDataProviders = value;
			}
		}

		private string _ExcelTemplateFilePath;
		/// <summary></summary>
		public string ExcelTemplateFilePath
		{
			get
			{
				return this._ExcelTemplateFilePath;
			}
			set
			{
				this._ExcelTemplateFilePath = value;
			}
		}

		private Dictionary<string, WorksheetTableBinding> _WorksheetDataBindings;
		/// <summary>map worksheet name to table name</summary>
		public Dictionary<string, WorksheetTableBinding> WorksheetDataBindings
		{
			get
			{
				return this._WorksheetDataBindings;
			}
			set
			{
				this._WorksheetDataBindings = value;
			}
		}

		private Dictionary<string, ChartDefinition> _Charts;
		/// <summary></summary>
		public Dictionary<string, ChartDefinition> Charts
		{
			get
			{
				return this._Charts;
			}
			set
			{
				this._Charts = value;
			}
		}
		#endregion

		#region ctor
		/// <summary>
		/// 
		/// </summary>
		public Excel2007ReportInstruction():base(Guid.Empty,Guid.Empty)
		{
			this._ExcelTemplateFilePath = string.Empty;
			this._ExcelReportFilePath = string.Empty;
			this._TableDataProviders=new List<TableDataFromSql>();
			this._WorksheetDataBindings=new Dictionary<string, WorksheetTableBinding>();
			this._Charts=new Dictionary<string, ChartDefinition>();
		}

		/// <summary>
		/// constructor, all derived class must call default constructor to initialize
		/// default settings.
		/// </summary>
		public Excel2007ReportInstruction(Guid componentID, Guid jobTicketID,
			string templatePath, string outputPath,
			List<TableDataFromSql> tableDataFromSqls,
			Dictionary<string,WorksheetTableBinding> worksheetBindings,
			Dictionary<string,ChartDefinition> charts) 
			: base(componentID, jobTicketID)
		{
			this._ExcelTemplateFilePath = templatePath;
			this._ExcelReportFilePath = outputPath;
			this._TableDataProviders = tableDataFromSqls;
			this._WorksheetDataBindings = worksheetBindings;
			this._Charts = charts;

		}
		#endregion

		#region Overrides of InstructionBase

		/// <summary>
		/// serialize settings to xml
		/// the function below is mainly for storing into database as serialized form instead of string
		/// </summary>
		public override XmlNode Serialize(ref XmlDocument xDoc, ref XmlNode parentNode)
		{
			XmlNode xNode = XmlDataUtil.AddElement(ref xDoc, parentNode, "Instruction");
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "Type", this.GetType().FullName);
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ComponentID", this.OwnerComponentID.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "WorkerID", this.OwnerWorkerInstanceID.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "SubmitTime", this.SubmitTime.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "StartTime", this.StartTime.ToString());

			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ExcelTemplateFilePath",this.ExcelTemplateFilePath);
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ReportFilePath", this.ExcelReportFilePath);

			// providers 
			if(this.TableDataProviders !=null && this.TableDataProviders.Count>0)
			{
				XmlNode providerRootNode = XmlDataUtil.AddElement(ref xDoc, xNode, "TableProviders");
				foreach(TableDataFromSql tableProvider in this.TableDataProviders)
				{
					XmlNode providerNode = XmlDataUtil.AddElement(ref xDoc, providerRootNode, "TableProvider");
					XmlDataUtil.UpdateAttribute(ref xDoc, providerNode,"ConnStr",tableProvider.ConnStr);
					XmlDataUtil.UpdateAttribute(ref xDoc, providerNode,"TableNames",StringUtil.ToString(tableProvider.OutputTableNames.ToArray()));
					XmlDataUtil.UpdateAttribute(ref xDoc, providerNode,"SelectSql",tableProvider.SelectSql);
					XmlDataUtil.UpdateAttribute(ref xDoc, providerNode,"IsStoredProc",tableProvider.IsStoredProc.ToString());
					if(tableProvider.Parameters !=null && tableProvider.Parameters.Count>0)
					{
						foreach(string paramName in tableProvider.Parameters.Keys)
						{
							XmlNode paramNode = XmlDataUtil.AddElement(ref xDoc, providerNode, "Parameter");
							XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterName", paramName);
							XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterValue",tableProvider.Parameters[paramName]);
						}
					}
				}
			}

			// binding 
			if(this.WorksheetDataBindings !=null && this.WorksheetDataBindings.Count>0)
			{
				XmlNode sheetNodes = XmlDataUtil.AddElement(ref xDoc, xNode, "Worksheets");
				foreach(string sheetName in this.WorksheetDataBindings.Keys)
				{
					WorksheetTableBinding sheetBinding = this.WorksheetDataBindings[sheetName];
					XmlNode sheetNode = XmlDataUtil.AddElement(ref xDoc, sheetNodes, "Worksheet");
					XmlDataUtil.UpdateAttribute(ref xDoc, sheetNode,"SheetName",sheetName);
					XmlDataUtil.UpdateAttribute(ref xDoc, sheetNode, "HeaderRowPosition",sheetBinding.ColumnHeaderRowPosition.ToString());
					XmlDataUtil.UpdateAttribute(ref xDoc, sheetNode,"KeyColumnPosition",sheetBinding.KeyColumnPosition.ToString());

					XmlNode tableNodes = XmlDataUtil.AddElement(ref xDoc, sheetNode, "Tables");
					foreach(string tableName in sheetBinding.TableNames)
					{
						XmlNode tableNode = XmlDataUtil.AddElement(ref xDoc, tableNodes, "Table");
						XmlDataUtil.UpdateAttribute(ref xDoc, tableNode,"TableName",tableName);
					}

					XmlNode colPosNodes = XmlDataUtil.AddElement(ref xDoc, sheetNode, "ColumnPositions");
					foreach(string colName in sheetBinding.ColumnPositions.Keys)
					{
						XmlNode colPosNode = XmlDataUtil.AddElement(ref xDoc, colPosNodes, "ColumnPosition");
						XmlDataUtil.UpdateAttribute(ref xDoc, colPosNode,"ColumnName",colName);
						XmlDataUtil.UpdateAttribute(ref xDoc, colPosNode,"Pos",sheetBinding.ColumnPositions[colName].ToString());
					}

					if(sheetBinding.TableKeyFields!=null && sheetBinding.TableKeyFields.Count>0)
					{
						XmlNode tableKeyNodes = XmlDataUtil.AddElement(ref xDoc, sheetNode, "TableKeys");
						foreach(string tableName in sheetBinding.TableKeyFields.Keys)
						{
							XmlNode keyNode = XmlDataUtil.AddElement(ref xDoc, tableKeyNodes, "TableKey");
							XmlDataUtil.UpdateAttribute(ref xDoc, keyNode,"TableName",tableName);
							XmlDataUtil.UpdateAttribute(ref xDoc, keyNode, "KeyField", sheetBinding.TableKeyFields[tableName]);
						}
					}
				}
			}

			// charts 
			if(this.Charts !=null && this.Charts.Count>0)
			{
				XmlNode chartNodes = XmlDataUtil.AddElement(ref xDoc, xNode, "Charts");
				foreach(string chartName in this.Charts.Keys)
				{
					ChartDefinition chartDefinition = this.Charts[chartName];
					XmlNode chartNode = XmlDataUtil.AddElement(ref xDoc, chartNodes, "Chart");
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "ChartName",chartName);
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "ChartType",
					                            Enum.GetName(typeof (ExcelChartType), chartDefinition.ChartType));
                    XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "ApplyStyle",chartDefinition.ApplyStyle.ToString());
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "ChartDataTable",chartDefinition.ChartDataTable);
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "SheetName", chartDefinition.SheetName);
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "Title", chartDefinition.Title);
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "CategoryColumn",chartDefinition.CategoryColumnName);
					XmlDataUtil.UpdateAttribute(ref xDoc, chartNode, "CategoryFormular",chartDefinition.CategoryFormular);
					if (chartDefinition.SeriesColumns != null && chartDefinition.SeriesColumns.Count > 0)
					{
						XmlNode seriesNodes = XmlDataUtil.AddElement(ref xDoc, chartNode, "SeriesCollection");
						foreach (string column in chartDefinition.SeriesColumns)
						{
							XmlNode seriesNode = XmlDataUtil.AddElement(ref xDoc, seriesNodes, "Series");
							seriesNode.InnerText = column;
						}
					}
					if(chartDefinition.SeriesDataFormulars!=null && chartDefinition.SeriesDataFormulars.Count>0)
					{
						XmlNode seriesFormularNodes = XmlDataUtil.AddElement(ref xDoc, chartNode, "SeriesFormulars");
						foreach(string seriesLabel in chartDefinition.SeriesDataFormulars.Keys)
						{
							XmlNode seriesFormularNode = XmlDataUtil.AddElement(ref xDoc, seriesFormularNodes, "SeriesFormular");
							XmlDataUtil.UpdateAttribute(ref xDoc, seriesFormularNode, "Label", seriesLabel);
							XmlDataUtil.UpdateAttribute(ref xDoc, seriesFormularNode, "Formular",chartDefinition.SeriesDataFormulars[seriesLabel]);
						}
					}
				}
			}

			return xNode;
		}

		/// <summary>
		/// instantiated from xml
		/// "&lt;Instructions&gt;&lt;Instruction&gt;&lt;/Instruction&gt;&lt;/Instruction&gt;", must
		/// instantiate from first child of root node
		/// </summary>
		public override InstructionBase Instantiate(XmlNode xNode)
		{
			if (xNode.Attributes.GetNamedItem("Type").Value == this.GetType().FullName)
			{
				Excel2007ReportInstruction instruction = new Excel2007ReportInstruction();
				instruction.OwnerComponentID = new Guid(xNode.Attributes.GetNamedItem("ComponentID").Value);
				instruction.OwnerWorkerInstanceID = new Guid(xNode.Attributes.GetNamedItem("WorkerID").Value);
				instruction.SubmitTime = DateTime.Parse(xNode.Attributes.GetNamedItem("SubmitTime").Value);
				instruction.StartTime = DateTime.Parse(xNode.Attributes.GetNamedItem("StartTime").Value);

				instruction.ExcelTemplateFilePath = XmlDataUtil.GetAttributeValue(xNode, "ExcelTemplateFilePath", "");
				instruction.ExcelReportFilePath = XmlDataUtil.GetAttributeValue(xNode, "ReportFilePath", "");
				XmlNodeList providerNodes = xNode.SelectNodes("TableProviders/TableProvider");

				// providers 
				instruction.TableDataProviders=new List<TableDataFromSql>();
				if(providerNodes !=null && providerNodes.Count>0)
				{
					foreach(XmlNode providerNode in providerNodes)
					{
						TableDataFromSql tblProvider=new TableDataFromSql();
						tblProvider.ConnStr = XmlDataUtil.GetAttributeValue(providerNode, "ConnStr", "");
						tblProvider.IsStoredProc =
							bool.Parse(XmlDataUtil.GetAttributeValue(providerNode, "IsStoredProc", false.ToString()));
						string[] tblNames = StringUtil.Split(XmlDataUtil.GetAttributeValue(providerNode, "TableNames", ""));
						tblProvider.OutputTableNames=new List<string>();
						tblProvider.OutputTableNames.AddRange(tblNames);
						tblProvider.SelectSql = XmlDataUtil.GetAttributeValue(providerNode, "SelectSql", "");
						XmlNodeList paramNodes = providerNode.SelectNodes("Parameter");
						if(paramNodes !=null && paramNodes.Count>0)
						{
							tblProvider.Parameters=new Dictionary<string, string>();
							foreach(XmlNode paramNode in paramNodes)
							{
								string paramName = XmlDataUtil.GetAttributeValue(paramNode, "ParameterName", "");
								string paramValue = XmlDataUtil.GetAttributeValue(paramNode, "ParameterValue", "");
								tblProvider.Parameters.Add(paramName,paramValue);
							}
						}
						instruction.TableDataProviders.Add(tblProvider);
					}
				}

				// bindings 
				instruction.WorksheetDataBindings = new Dictionary<string, WorksheetTableBinding>();
				XmlNodeList bindingNodes = xNode.SelectNodes("Worksheets/Worksheet");
				if(bindingNodes !=null && bindingNodes.Count>0)
				{
					foreach(XmlNode sheetNode in bindingNodes)
					{
						WorksheetTableBinding binding=new WorksheetTableBinding();
						binding.WorksheetName = XmlDataUtil.GetAttributeValue(sheetNode, "SheetName", "");
						binding.ColumnHeaderRowPosition = int.Parse(XmlDataUtil.GetAttributeValue(sheetNode, "HeaderRowPosition", "1"));
						binding.KeyColumnPosition = int.Parse(XmlDataUtil.GetAttributeValue(sheetNode, "KeyColumnPosition", "1"));
						List<string> tblNames=new List<string>();
						XmlNodeList tblNodes = sheetNode.SelectNodes("Tables/Table");
						foreach(XmlNode tblNode in tblNodes)
						{
							string tblName = XmlDataUtil.GetAttributeValue(tblNode, "TableName", "");
							tblNames.Add(tblName);
						}
						binding.TableNames = tblNames;
						XmlNodeList colPosNodes = sheetNode.SelectNodes("ColumnPositions/ColumnPosition");
						Dictionary<string,int> colPositions=new Dictionary<string, int>();
						foreach(XmlNode colPosNode in colPosNodes)
						{
							string colName = XmlDataUtil.GetAttributeValue(colPosNode, "ColumnName", "");
							int pos = int.Parse(XmlDataUtil.GetAttributeValue(colPosNode, "Pos", "0"));
							colPositions.Add(colName,pos);
						}
						binding.ColumnPositions = colPositions;
						XmlNodeList tblKeyNodes = sheetNode.SelectNodes("TableKeys/TableKey");
						if(tblKeyNodes !=null && tblKeyNodes.Count>0)
						{
							Dictionary<string,string> tblKeys=new Dictionary<string, string>();
							foreach(XmlNode tblKeyNode in tblKeyNodes)
							{
								string tblName = XmlDataUtil.GetAttributeValue(tblKeyNode, "TableName", "");
								string keyField = XmlDataUtil.GetAttributeValue(tblKeyNode, "KeyField", "");
								tblKeys.Add(tblName,keyField);
							}
							binding.TableKeyFields = tblKeys;
						}
						instruction.WorksheetDataBindings.Add(binding.WorksheetName,binding);
					}
				}

				// charts 
				instruction.Charts = new Dictionary<string, ChartDefinition>();
				XmlNodeList chartNodes = xNode.SelectNodes("Charts/Chart");
				if(chartNodes !=null && chartNodes.Count>0)
				{
					foreach(XmlNode chartNode in chartNodes)
					{
						ChartDefinition chartDef=new ChartDefinition();
						chartDef.ChartName = XmlDataUtil.GetAttributeValue(chartNode, "ChartName", "");
						chartDef.ChartType =
							(ExcelChartType)
							Enum.Parse(typeof (ExcelChartType),
							           XmlDataUtil.GetAttributeValue(chartNode, "ChartType", ExcelChartType.BarChart.ToString()));
					    chartDef.ApplyStyle = bool.Parse(XmlDataUtil.GetAttributeValue(chartNode, "ApplyStyle", false.ToString()));
						chartDef.ChartDataTable = XmlDataUtil.GetAttributeValue(chartNode, "ChartDataTable", "");
						chartDef.SheetName = XmlDataUtil.GetAttributeValue(chartNode, "SheetName", "");
						chartDef.Title = XmlDataUtil.GetAttributeValue(chartNode, "Title", "");
						chartDef.CategoryColumnName = XmlDataUtil.GetAttributeValue(chartNode, "CategoryColumn", "");
						chartDef.CategoryFormular = XmlDataUtil.GetAttributeValue(chartNode, "CategoryFormular", "");
						XmlNodeList seriesNodes = chartNode.SelectNodes("SeriesCollection/Series");
						if(seriesNodes !=null && seriesNodes.Count>0)
						{
							chartDef.SeriesColumns=new List<string>();
							foreach(XmlNode seriesNode in seriesNodes)
							{
								string column = seriesNode.InnerText;
								chartDef.SeriesColumns.Add(column);
							}
						}
						XmlNodeList seriesFormularNodes = chartNode.SelectNodes("SeriesFormulars/SeriesFormular");
						if(seriesFormularNodes !=null && seriesFormularNodes.Count>0)
						{
							chartDef.SeriesDataFormulars=new Dictionary<string, string>();
							foreach(XmlNode formularNode in seriesFormularNodes)
							{
								string label = XmlDataUtil.GetAttributeValue(formularNode, "Label", "");
								string formular = XmlDataUtil.GetAttributeValue(formularNode, "Formular", "");
								chartDef.SeriesDataFormulars.Add(label,formular);
							}
						}
						instruction.Charts.Add(chartDef.ChartName, chartDef);
					}
				}

				return instruction;
			}
			else
			{
				throw new Exception("Unable to instantiate instruction: invalid type specified in xml node");
			}
		}

		#endregion
	}
}
